/**
 * CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

package com.cmcc.zysoft.infocollection.util;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.commons.lang.StringUtils;

/**
 * @author 袁凤建
 * <br />邮箱: yuan.fengjian@ustcinfo.com
 * <br />描述: ImportData.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-9-28 下午3:59:39
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

public class ImportData {

	public static void testConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/infocollection";
			Connection con = DriverManager.getConnection(url, "root", "Yuan@6583149");
			System.out.println(con);
			if(null != con) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void readData() {
		try {
			File file = new File("C:\\标签2003.xls");
			ExcelOpen readExcel = new ExcelOpen(file);
			readExcel.open();
			// 设置读取索引为0的工作表
			readExcel.setSheetNum(0);
			// 总行数
			int count = readExcel.getRowCount();
			for (int i = 0; i <= count; i++) {
				Object[] rows = readExcel.readExcelLine(i);
				if(null == rows || rows.length < 6) {
					continue;
				}
				System.out.println(rows[5]);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void importData() {
		try {
			PreparedStatement pstmt = null;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/infocollection";
			Connection con = DriverManager.getConnection(url, "root", "Yuan@6583149");
			
			String sql = "insert into tb_b_tag(tag_id, tag_name) values( ?, ? )";
			
			String name = "";
			File file = new File("C:\\标签2003.xls");
			ExcelOpen readExcel = new ExcelOpen(file);
			readExcel.open();
			// 设置读取索引为0的工作表
			readExcel.setSheetNum(0);
			// 总行数
			int count = readExcel.getRowCount();
			for (int i = 0; i <= count; i++) {
				String[] rows = readExcel.readExcelLine(i);
				if(null == rows || rows.length < 2) {
					continue;
				}
				name = rows[0];
				System.out.println(String.valueOf(i + 1) + " : " + name);
				if(StringUtils.isNotBlank(name)) {
					pstmt = con.prepareStatement(sql);
					pstmt.setString(1, String.valueOf(i + 1));
					pstmt.setString(2, name.trim());
					pstmt.execute();
				}
			}
			if(null != pstmt){
				pstmt.close();
			}
			if(null != con){
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		ImportData.importData();
	}
}